package com.sprite.framework.entity.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.sprite.framework.entity.DataScriptStatement;
import com.sprite.framework.entity.EntityScript;
import com.sprite.framework.entity.EntityScriptExecutor;
import com.sprite.framework.entity.model.ModelEntity;
import com.sprite.framework.entity.model.ModelEntityUtil;
import com.sprite.framework.entity.model.ModelField;
import com.sprite.utils.UtilMisc;
import com.sprite.utils.UtilString;

/**
 * @author Jack
 */
public final class DatabaseUtil {
	private Logger logger = LoggerFactory.getLogger(DatabaseUtil.class);

	private DataSource dataSource;

	private EntityScriptExecutor scriptExecutor;


	/**
	 *	检查数据库，生成或更新表结构
	 * @param entityList 实体结构
	 * @throws SQLException sql执行错误
	 */
	public void checkDb(List<ModelEntity> entityList) throws SQLException {
		Connection connection = dataSource.getConnection();
		DbInfo dbInfo = getDbInfo(connection);

		String namespace = DbObjects.namespace(connection.getCatalog(), connection.getSchema());

		List<ModelEntity> listToCreate = new LinkedList<>();
		List<ColumnCheckInfo> listToadd = new LinkedList<>();

		DbObjects colums = dbInfo.getColums(namespace);
		DbObjects tables = dbInfo.getTables(namespace);


		for(ModelEntity modelEntity : entityList) {
			String tableName = modelEntity.getTableName().toLowerCase();
			if(tables == null || !tables.contains(tableName)){
				listToCreate.add(modelEntity);
				continue;
			}
			for(ModelField modelField : modelEntity.getFields()) {
				if(colums == null || !colums.contains(UtilString.place("{}.{}", tableName, modelField.getColName().toLowerCase()))) {
					listToadd.add(new ColumnCheckInfo(tableName, modelField.getColName(), modelField.getType()));
				}
			}
		}

		// 创建表
		for(ModelEntity entity : listToCreate) {
			scriptExecutor.execute(new InsertTableScript(entity));
			logger.info("create table :{}",entity.getTableName());
		}

		// 更新表
		for(ColumnCheckInfo entity : listToadd) {
			scriptExecutor.execute(new AddColumnScript(entity));
			logger.info("add column :{}, table:{} ",entity.columnName, entity.tableName);
		}
	}

	private DbInfo getDbInfo(Connection connection) throws SQLException{
		DbInfo dbInfo = new DbInfo();

		DatabaseMetaData databaseMetaData = connection.getMetaData();

		ResultSet tableSet  = databaseMetaData.getColumns(null, "%", "%", "%");
		while(tableSet.next()){
			String catalog = tableSet.getString("TABLE_CAT");
			String schema = tableSet.getString("TABLE_SCHEM");
			String table = tableSet.getString("TABLE_NAME").toLowerCase();
			String column = tableSet.getString("COLUMN_NAME").toLowerCase();

			String namespace = DbObjects.namespace(catalog, schema);

			DbObjects dbObjects = dbInfo.getTables(namespace);
			if(dbObjects == null){
				dbObjects = new DbObjects(catalog, schema);
				dbInfo.addTables(dbObjects);
			}
			dbObjects.add(table);


			dbObjects = dbInfo.getColums(namespace);
			if(dbObjects == null){
				dbObjects = new DbObjects(catalog, schema);
				dbInfo.addColumns(dbObjects);
			}
			dbObjects.add(UtilString.place("{}.{}", table, column));
		}

		return dbInfo;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public EntityScriptExecutor getScriptExecutor() {
		return scriptExecutor;
	}

	public void setScriptExecutor(EntityScriptExecutor scriptExecutor) {
		this.scriptExecutor = scriptExecutor;
	}

	private static class DbInfo{
		// key is namespace
		private Map<String, DbObjects> tables = new HashMap<>();
		private Map<String, DbObjects> columns = new HashMap<>();

		public DbObjects getTables(String namespace){
			return tables.get(namespace);
		}

		public DbObjects getColums(String namespace){
			return columns.get(namespace);
		}

		public void addTables(DbObjects tables){
			this.tables.put(tables.getNamesapce(), tables);
		}

		public void addColumns(DbObjects columns){
			this.columns.put(columns.getNamesapce(), columns);
		}

	}

	/**
	 * 数据库对象名称集合
	 */
	private static class DbObjects extends HashSet<String> {

		private String namesapce;
		private String catalog;
		private String schema;

		DbObjects(String catalog, String schema){
			this.catalog = catalog;
			this.schema = schema;
			this.namesapce = namespace(catalog, schema);
		}

		public static String namespace(String catalog, String schema){
			return UtilString.place("{}.{}", catalog!=null?catalog:"", schema!=null?schema:"");
		}

		public String getNamesapce() {
			return namesapce;
		}

		public String getCatalog() {
			return catalog;
		}

		public String getSchema() {
			return schema;
		}
	}

	private static class ColumnCheckInfo {
		public String tableName;
		public String columnName;
		public String type;

		public ColumnCheckInfo(String tableName, String columnName, String type) {
			this.tableName = tableName;
			this.columnName = columnName;
			this.type = type;
		}
	}

	private static class InsertTableScript implements EntityScript{

		List<ModelEntity> entityList;

		public InsertTableScript(ModelEntity modelEntity) {
			super();
			this.entityList = UtilMisc.toList(modelEntity);
		}

		@Override
		public DataScriptStatement getStatement() {
			DataScriptStatement scriptStatement = new DataScriptStatement();

			for(ModelEntity entity : entityList) {
				scriptStatement.append("CREATE TABLE ").append(entity.getTableName()).append("(");

				List<ModelField> fields = entity.getFields();
				int lg = fields.size();
				for(ModelField modelField : fields) {
					scriptStatement.append(modelField.getColName()).append(" ").append(ModelEntityUtil.getFieldType(modelField.getType()).getSqlType());
					if(lg>1) {
						scriptStatement.append(",");
					}
					lg--;
				}

				if(!entity.getKeys().isEmpty()) {
					scriptStatement.append(",PRIMARY KEY(");
					lg = entity.getKeys().size();
					for(ModelField modelField : entity.getKeys()) {
						scriptStatement.append(modelField.getColName());
						if(lg>1) {
							scriptStatement.append(",");
						}
						lg--;
					}
					scriptStatement.append(")");
				}
				scriptStatement.append(");");
			}
			return scriptStatement;
		}
	}

	private static class AddColumnScript implements EntityScript{

		List<ColumnCheckInfo> columnList;

		public AddColumnScript(ColumnCheckInfo columnList) {
			this.columnList = UtilMisc.toList(columnList);
		}

		@Override
		public DataScriptStatement getStatement() {
			DataScriptStatement scriptStatement = new DataScriptStatement();

			for(ColumnCheckInfo column : columnList) {
				scriptStatement.append("ALTER  TABLE ").append(column.tableName).append(" ADD ").append(column.columnName);
				scriptStatement.append(" ").append(ModelEntityUtil.getFieldType(column.type).getSqlType()).append(";");
			}
			return scriptStatement;
		}
	}
}
